package com.price.compare.common;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

public class LargeExcelExportUtil {

    private static Logger logger = LoggerFactory.getLogger(LargeExcelExportUtil.class);

    /**
     * 不生成文件
     * 直接导出
     * @param newFileName
     * @param objList
     * @param headers
     * @param keys
     * @param response
     * @throws Exception
     */
    public static void exportExcel(String newFileName, List<?> objList, String[] headers,String[] keys, HttpServletResponse response) {
        JSONArray dataList = JSONUtil.parseArray(objList);
        exportExcelByTemplate(null, newFileName, dataList,headers, keys, response);
    }


    /**
     * 不生成文件
     * 直接导出
     *
     * @param templatePath      模板路径
     * @param newFileName       新文件的名称，不加后缀
     * @param dataList          数据
     * @param keys              标题
     * @return
     */
    public static void exportExcelByTemplate(String templatePath, String newFileName, JSONArray dataList,String[] headers, String[] keys,
                                             HttpServletResponse response){
        SXSSFWorkbook wb;
        try{
            wb = createWorkbookByTemplate(templatePath);
        }catch (Exception e){
            throw new RuntimeException("Excel创建出错：" + e.getMessage());
        }
        Sheet sheet = null;
        try{
            sheet = wb.getSheetAt(0);
        }catch (Exception e){
            sheet = wb.createSheet();
        }
        if(null == sheet){
            sheet = wb.createSheet();
        }

        // 表头
        int rowNum = 0;
        if (null != headers && 0 < headers.length) {
            Row headRow = sheet.createRow(rowNum);
            int len = headers.length;
            for (int i = 0; i < len; i++) {
                Cell cell = headRow.createCell(i);
                cell.setCellValue(headers[i]);
            }
            rowNum++;
        }

        if (null != dataList) {
            setExcelSheetDataByRow(wb, sheet, dataList, keys, rowNum);
        }

        //导出数据
        OutputStream outputStream = null;
        try {
            //设置Http响应头告诉浏览器下载这个附件
            newFileName = newFileName + ".xlsx";
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Content-Disposition", "attachment;Filename=" + newFileName + ";filename*=utf-8''" + newFileName);
            outputStream = response.getOutputStream();

            wb.write(outputStream);
            outputStream.close();
        } catch (Exception ex) {
            logger.info("导出Excel错误:", ex);
            throw new RuntimeException("导出Excel出现严重异常，异常信息：" + ex.getMessage());
        } finally {
            if (null != wb) {
                wb.dispose(); // 删除临时文件
            }
            try {
                if (null != outputStream) {
                    outputStream.close();
                }
            } catch (Exception e1) {
            }
            try {
                if (null != wb) {
                    wb.close();
                }
            } catch (Exception e1) {
            }
        }
    }


    public static void setHeaders(Sheet sheet,String[] headers){
        // 表头
        if (null != headers && 0 < headers.length) {
            Row headRow = sheet.createRow(0);
            int len = headers.length;
            for (int i = 0; i < len; i++) {
                Cell cell = headRow.createCell(i);
                cell.setCellValue(headers[i]);
            }
        }
    }



    /**
     * 根据模板创建工作簿
     *
     * @param templatePath
     * @return
     * @throws Exception
     */
    public static SXSSFWorkbook createWorkbookByTemplate(String templatePath) throws Exception {

        if(StringUtils.isEmpty(templatePath)){
            SXSSFWorkbook wb = new SXSSFWorkbook();
            wb.setCompressTempFiles(false);
            return wb;
        }

        Resource resource = new ClassPathResource(templatePath);
        InputStream in = resource.getInputStream();
        try {
            XSSFWorkbook templateWb = new XSSFWorkbook(in);
            SXSSFWorkbook wb = new SXSSFWorkbook(templateWb, 100);
            wb.setCompressTempFiles(false);
            return wb;
        } catch (Exception e) {
            logger.info("根据模板创建Excel流错误", e);
        }
        return null;
    }

    /**
     * 创建行
     *
     * @param row
     * @param jo
     * @param keys
     */
    public static void setRowValue(Row row, JSONObject jo, String[] keys, Workbook wb,CellStyle dateCellStyle) {
        int len = keys.length;
        for (int j = 0; j < len; j++) {
            if (null == jo.get(keys[j])) {
                continue;
            }
            Cell cell = row.getCell(j);
            if (null == cell) {
                cell = row.createCell(j);
            }
            setCellValue(jo, keys[j], cell, wb,dateCellStyle);
        }
    }

    public static void setCellValue(JSONObject jo, String key, Cell cell, Workbook wb,CellStyle dateCellStyle) {
        Object value = jo.get(key);
        if (null == value) {
            return;
        } else if (value.getClass().equals(BigDecimal.class)) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(value.toString()));
        } else if (value.getClass().equals(Date.class)) {
            cell.setCellStyle(dateCellStyle);
            Date date = jo.getDate(key);
            cell.setCellValue(date);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    /**
     * 按行设置数据
     *
     * @param sheet
     * @param array
     */
    public static void setExcelSheetDataByRow(Workbook wb, Sheet sheet, JSONArray array, String[] keys, Integer dataRowFirstIndex) {
        if (null == array) {
            return;
        }
        int size = array.size();
        if (null == dataRowFirstIndex) {
            dataRowFirstIndex = 1;
        }
        CellStyle dateCellStyle = wb.createCellStyle();
        String defaultFormat = "yyyy/MM/dd";
        dateCellStyle.setDataFormat(wb.createDataFormat().getFormat(defaultFormat));  // 日期的显示格式
        for (int i = 0; i < size; i++) {  // 行
            JSONObject obj = array.getJSONObject(i);
            int rowIndex = i + dataRowFirstIndex;
            Row row = sheet.getRow(rowIndex);
            if (null == row) {
                row = sheet.createRow(rowIndex);
            }
            setRowValue(row, obj, keys, wb,dateCellStyle);
        }
    }

    /**
     * 设置标题行
     *
     * @param row
     * @param headers
     */
    public static void setCellData(Row row, String[] headers) {
        int len = headers.length;
        for (int i = 0; i < len; i++) {
            Cell cell = row.getCell(i);
            if (null == cell) {
                cell = row.createCell(i);
            }
            cell.setCellValue(headers[i]);
        }
    }

    /**
     * 设置合并单元格
     *
     * @param sheet
     * @param mergedList
     */
    private static void setMergeCell(Sheet sheet, List<CellRangeAddress> mergedList) {
        if (null != mergedList && 0 < mergedList.size()) {
            int mLen = mergedList.size();
            for (int i = 0; i < mLen; i++) {
                sheet.addMergedRegion(mergedList.get(i));
            }
        }
    }

}
